package leave.dataaccess.mysql;

import java.sql.*;

import java.util.ArrayList;
import java.util.Date;

import leave.dataaccess.PublicHolidayDAO;
import leave.exception.DataAccessException;
import leave.model.PublicHoliday;
import leave.model.impl.mysql.*;

import leave.util.*;

public class PublicHolidayDAOImpl implements PublicHolidayDAO {

	private Connection FactoryConnection;
	
	public PublicHolidayDAOImpl(Connection factoryConnection) {
		super();
		FactoryConnection = factoryConnection;
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.PublicHolidayDAO#isPublicHoliday(java.util.Date)
	 * Checks if the day given is the public holiday.
	 */
	@Override
	public boolean isPublicHoliday(Date date) throws DataAccessException {
		boolean result = false;
		Statement st = null;
		String selectSql = "select date from public_holiday";
		try {
			st = FactoryConnection.createStatement();
			ResultSet rs = st.executeQuery(selectSql);  
			while (rs.next()) {
				if (rs.getDate("date").equals(
						java.sql.Date.valueOf(("date"))))
					result = true;
			}
		} catch (Exception e) {
			String error = "Unable to carry out transation." + e;
			throw new DataAccessException(error);
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				String error = "Unabel to carry out transaction" + e;
				throw new DataAccessException(error);
			}
		}
		return result;
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.PublicHolidayDAO#getPublicHolidays(java.util.Date, java.util.Date)
	 * Gets the list of public holidays in the given period.
	 */
	@Override
	public PublicHoliday[] getPublicHolidays(Date startDate,
			Date endDate) throws DataAccessException {
		Statement st = null;
		ArrayList<PublicHoliday> result = new ArrayList<PublicHoliday>();
		String startdateString = DateConvertTo.getSQLDate(startDate).toString();
		String enddateString = DateConvertTo.getSQLDate(endDate).toString();
		/*
		String startdate = startDate.getYear() + "-" + (startDate.getMonth()+1)
				+ "-" + startDate.getDate();
		String enddate = startDate.getYear() + "-" + (startDate.getMonth()+1) + "-"
				+ startDate.getDate();
		*/
		
		String selectSql = "select * from public_holiday WHERE date BETWEEN '"
				+ startdateString + "' AND '" + enddateString + "'";
		ResultSet rs = null;
		
		try {
			st = FactoryConnection.createStatement();
			rs = st.executeQuery(selectSql);
			while (rs.next()) {
				PublicHoliday holiday = new PublicHolidayImpl();
				holiday.setDate(rs.getDate("date"));
				holiday.setDescription(rs.getString("description"));

				result.add(holiday);
			}
		} catch (SQLException e) {

		}
		finally {
			try {
				st.close();
			} catch (SQLException e) {
				String error = "Unabel to carry out transaction" + e;
				throw new DataAccessException(error);
			}
		}

		return (PublicHoliday[])result.toArray();
	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.PublicHolidayDAO#insertPublicHoliday(leave.model.PublicHoliday)
	 * Adds a new Public Holiday to the database.
	 */
	@Override
	public void insertPublicHoliday(PublicHoliday publicHoliday) throws DataAccessException {
		Statement st = null;
		String insertSql="Insert into public_holiday values ('"+publicHoliday.getDate()+"','"+publicHoliday.getDescription()+"');";
		try
		{
			st = FactoryConnection.createStatement();
			st.executeUpdate(insertSql);

		}catch(Exception e)
		{
			String error="Unable to carry out transaction. ";
			throw new DataAccessException(error + e);
		}
		finally
		{
			try{
				st.close();
			}catch(Exception e)
			{
				String error="Unable to carry out transaction. ";
				throw new DataAccessException(error+e);
			}
		}

	}

	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.PublicHolidayDAO#updatePublicHoliday(leave.model.PublicHoliday)
	 * Updates the public holiday in the database.
	 */
	@Override
	public void updatePublicHoliday(PublicHoliday publicHoliday) throws DataAccessException {
		Statement st = null;
		String updateSql="UPDATE public_holiday set description='"+publicHoliday.getDescription()+"' where date='"+publicHoliday.getDate()+"'";
		try
		{
			st= FactoryConnection.createStatement();
			st.executeUpdate(updateSql);

		}catch(Exception e)
		{
			String error="Unable to carry out transaction. ";
			throw new DataAccessException(error+e);
		}
		finally
		{
			try{
				st.close();
			}catch(Exception e)
			{
				String error="Unable to carry out transaction. ";
				throw new DataAccessException(error+e);
			}
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see leave.dataaccess.PublicHolidayDAO#deletePublicHoliday(leave.model.PublicHoliday)
	 * Removes the public holiday data from the database.
	 */
	@Override
	public void deletePublicHoliday(PublicHoliday publicHoliday) throws DataAccessException {
		String deleteSql="delete from public_holiday where date='"+publicHoliday.getDate()+"'";
		Statement st = null;
		
		try
		{
			st = FactoryConnection.createStatement();
			st.executeUpdate(deleteSql);
			
		}catch(Exception e)
		{
			String error = "Unable to carry out transaction. ";
			throw new DataAccessException(error + e);
		}
		finally
		{
			try{
			st.close();
			}catch(Exception e)
			{
				String error = "Unable to carry out transaction. ";
				throw new DataAccessException(error + e);
			}
		}

	}

	@Override
	public PublicHoliday createPublicHolidays() {
		return new PublicHolidayImpl();
	}

}
